"""
@Uses: This file is used to get
"""
import os
import MySQLdb
import sys
import xml.etree.cElementTree as ElementTree
reload(sys)
sys.setdefaultencoding('utf-8')

"""
site_file = "temp_sites"#"all_site_name"
table_type = "users"
file_path = "D:\Research\ISEC-2014\data\content"
database = "isec2014"
user = "root"
password="123"
port=3306

"""
site_file = "all_site_name"
table_type = "users"
file_path = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content"
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
#"""

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor() 

file = open(site_file)
for line in file:
    site = line.strip() +".com"
    print  "site=",site
    table  = table_type+"_"+line.strip()+"_aug13"
    
    source = file_path+"\\"+site+"\\"+table_type+".xml"
    # get an iterable
    context = ElementTree.iterparse(source, events=("start", "end"))

    # turn it into an iterator
    context = iter(context)

    # get the root element
    event, root = context.next()
    count1 = 0 
    
    for event, child_of_root in context:
        if event == "end" and child_of_root.tag == "row":
            id= (str)(child_of_root.attrib['Id'])
            #print "id=",id
            count = 0
        
            try:
                email_hash  = (child_of_root.attrib['EmailHash'])
            except Exception, e:
                email_hash = "-1"    
                #print "val=", val  
            try: 
                web_url = (str)(child_of_root.attrib['WebsiteUrl'])
            except Exception, e:
                web_url = "-1"  
      
              
            try: 
                age = (str)(child_of_root.attrib['Age'])
            except Exception, e:
                age = "-1"  
        
            try: 
                location = (str)(child_of_root.attrib['Location'])
                location = location.replace("\n"," ")
                location = location.replace("'"," ")   
                location = location.replace(","," ")  
                location = location.replace("\\"," ")  
            except Exception, e:
                location = "-1"
         
            
            
            try: 
                about_me = (str)(child_of_root.attrib['AboutMe'])
                about_me = about_me.replace("\n"," ")
                about_me = about_me.replace("'"," ")
                about_me = about_me.replace("\""," ")
                about_me = about_me.replace(",", " ")
                about_me = about_me.replace("(", " ")
                about_me =about_me.replace(")", " ")
                about_me = about_me.replace("\\", " ")
            except Exception, e:
                about_me = "-1"        
            
        
            display_name = (str)(child_of_root.attrib['DisplayName']).replace("'"," ")      
            
            insert_str = "insert into `"+ table+ "` values("+child_of_root.attrib['Id']+","+child_of_root.attrib['Reputation']+\
            ",'"+child_of_root.attrib['CreationDate']+"','"+ display_name+"','"+\
            child_of_root.attrib['LastAccessDate']+"','"+web_url+"','"+\
            about_me+"','"+location+"',"+\
            child_of_root.attrib['Views']+","+child_of_root.attrib['UpVotes']+","+child_of_root.attrib['DownVotes']\
            +",'"+email_hash+"',"+age+")"
         
            #print "insert = ", insert_str
            insert_cursor.execute(insert_str)
        root.clear()
    
    db1.commit()
    
    